package com.weixin.dao.impl;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Iterator;
import java.util.List;
import java.util.Map;
import java.util.Set;

import org.apache.commons.dbutils.handlers.BeanHandler;
import org.apache.commons.dbutils.handlers.BeanListHandler;
import org.apache.commons.dbutils.handlers.ScalarHandler;

import com.weixin.dao.IZhuanZhangDAO;
import com.weixin.util.C3p0Util;
import com.weixin.vo.Bank;
import com.weixin.vo.Users;
import com.weixin.vo.ZhuanZhang;

public class ZhuanZhangImpl implements IZhuanZhangDAO{

	/**
	 * 将zhuanzhangtable和user_table以及bank_table联立，通过user_id和bank_id获得会员姓名和银行名称
	 * @return
	 * @throws SQLException 
	 */
	@Override
	public List<ZhuanZhang> selectAll() throws SQLException {
		return C3p0Util.qr.query("select * \r\n" + 
				"from zhuanzhangtable\r\n" + 
				"left join user_table\r\n" + 
				"on user_table.user_id=zhuanzhangtable.user_id\r\n" + 
				"left join bank_table\r\n" + 
				"on zhuanzhangtable.bank_id = bank_table.bank_id ",new BeanListHandler<ZhuanZhang>(ZhuanZhang.class));

	}

	/**
	 * 将bank_table里的数据遍历，制作成下拉框
	 * @return
	 * @throws SQLException
	 */
	@Override
	public List<Bank> findALl() throws SQLException {
		return C3p0Util.qr.query("select * from bank_table", new BeanListHandler<Bank>(Bank.class));
		  
	}

	/**
	 * 分页
	 * @param pageNum  页码
	 * @param pageSize  每页有几条数据
	 * @return
	 * @throws SQLException
	 */
	@Override
	public List<ZhuanZhang> findBySplit(int pageNum, int pageSize) throws SQLException {
		return C3p0Util.qr.query("select * from zhuanzhangtable order by trade_id desc limit ?,?;",new BeanListHandler<ZhuanZhang>(ZhuanZhang.class),(pageNum-1)*pageSize,pageSize);
	}

	/**
	 * 计算总条数
	 * @return
	 * @throws SQLException
	 */
	@Override
	public int findTotal() throws SQLException {
		Connection connection = C3p0Util.getConnection();
        PreparedStatement preparedStatement = (PreparedStatement) connection.prepareStatement("select count(*) from zhuanzhangtable");
        ResultSet resultSet = preparedStatement.executeQuery();
        resultSet.next();
        int n = resultSet.getInt(1);
        connection.close();
        return n;
	}

	/**
	 * g根据交易单号删除
	 * @param trade_id 交易单号
	 * @return
	 * @throws SQLException
	 */
	@Override
	public boolean removeById(int trade_id) throws SQLException {
		return C3p0Util.qr.execute("delete from zhuanzhangtable where trade_id=?",trade_id)>0;
	}

	
	/**
	 * 删除一列数据
	 * @param trade_ids
	 * @return
	 * @throws SQLException
	 */
	@Override
	public boolean removeAll(String[] trade_ids) throws SQLException {
		StringBuffer sql = new StringBuffer("delete from zhuanzhangtable where trade_id in (");
        //保存值：
        List<String> valueList = new ArrayList<>();

        //遍历数组：
        for(String trade_id:trade_ids){
            sql.append("?,");
            valueList.add(trade_id);
        }
        //删除最后一个逗号
        sql.deleteCharAt(sql.length()-1);
        sql.append(")");
        return  C3p0Util.qr.execute(sql.toString(),valueList.toArray())>0;
	}

	/**
     * 根据员工id查找信息
     * @throws SQLException 
     */
	@Override
	public ZhuanZhang findById(int trade_id) throws SQLException {
		return C3p0Util.qr.query("select temp.*\r\n" + 
				"from (select * \r\n" + 
				"	from zhuanzhangtable\r\n" + 
				"	left join user_table\r\n" + 
				"	on user_table.user_id=zhuanzhangtable.user_id\r\n" + 
				"	left join bank_table\r\n" + 
				"	on zhuanzhangtable.bank_id = bank_table.bank_id)temp\r\n" + 
				"where trade_id=?", new BeanHandler<ZhuanZhang>(ZhuanZhang.class),trade_id);
	}

	/**
     * 带搜索分页
     * @param pageNum   页号
     * @param pageSize  页面大小
     * @param paramMap  所有参数
     * @return
     * @throws SQLException
     */
	@Override
	public List<ZhuanZhang> selectMemberInfo(String user_name, int pageNum, int pageSize) throws SQLException {
		List<ZhuanZhang> list = new ArrayList<>();
		StringBuffer sql = new StringBuffer("select * from zhuanzhangtable ");
		if(user_name==null||user_name.equals("")) {
			sql.append(" limit ?,?");
			list = C3p0Util.qr.query(sql.toString(), new BeanListHandler<ZhuanZhang>(ZhuanZhang.class),(pageNum-1)*pageSize,pageSize);
		}else {
			sql.append(" where zhuanzhangtable.user_name=?  limit ?,?");
			list = C3p0Util.qr.query(sql.toString(),new BeanListHandler<ZhuanZhang>(ZhuanZhang.class),user_name,(pageNum-1)*pageSize,pageSize);
		}
		return list;
	}

	 /**
     * 搜索到的东西总页面的
     * @param paramMap
     * @return
     * @throws SQLException
     */
	@Override
	public long selectMemberCount(String user_name) throws SQLException {
		long n = 0;
		if(user_name==null||("".equals(user_name))) {
			n= (long)C3p0Util.qr.query("select count(*) from zhuanzhangtable",new ScalarHandler<>());
		}else {
			n= (long)C3p0Util.qr.query("select count(*) \r\n" + 
					"from zhuanzhangtable\r\n" + 
					"where user_name=?	",new ScalarHandler<>(),user_name);
		}
		return n;
	}

	 /**
     * 往转账表里插入数据
     * @param vo
     * @return
     * @throws SQLException
     */
	@Override
	public boolean insert(ZhuanZhang vo) throws SQLException {
		boolean b = C3p0Util.qr.execute("insert into zhuanzhangtable(user_name,bank_name,money,user_money,time) values(?,?,?,?,?)",vo.getUser_name(),vo.getBank_name(),vo.getMoney(),vo.getUser_money(),vo.getTime())>0;
		return b;
	}

	 
    /**
     * 根据账号和密码修改余额
     * @param vo
     * @return
     * @throws SQLException
     */
	@Override
	public boolean update(Users vo) throws SQLException {
		boolean b = C3p0Util.qr.execute("update user_table set user_money = ? where user_name=? and user_pwd=?", vo.getUser_money(),vo.getUser_name(),vo.getUser_pwd())>0;
		return b;
	}
	
	

}
